<?php

class StatsController extends AppController {

    var $name = 'Stats';
    var $uses = array('Stat',
        'Job',
        'Employee',
        'Theater',
        'Expense',
        'Costume',
        'Show',
        'Pelotero',
        'Provider',
        'Service',
        'Cash');

    function beforeFilter() {
        parent::beforeFilter();
        // Limito que si es un usuario del tipo user, solo pueda accedear a la 
        //estadística por empleado
        $accion = $this->action;
        if ($this->Auth->user('group_id') == GROUP_USER && !in_array($accion, array('employee', 'imprimir_employee_stats', 'pay_all', 'provider'))) {
            $this->Session->setFlash(__('Acceso Restringido', true));
            $this->redirect(array('controller' => 'futures', 'action' => 'index'));
        }
    }

    function general() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $filtro_theater = '';
        $filtro_expense = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['pelotero_id']) && trim($datos['pelotero_id']) != '') {
                if ($datos['pelotero_id'] == 1) {
                    //solo los trabajos a particulares
                    $filtro_birthday .= ' AND Job.pelotero_id IS NULL';
                    $this->data['Stat']['particular'] = 1;
                } else {
                    //solo los trabajos a peloteros
                    $filtro_birthday .= ' AND Job.pelotero_id IS NOT NULL';
                    $this->data['Stat']['particular'] = 2;
                }
            }
            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $filtro_theater .= ' AND Theater.start_date >= "' . $datos['date_from'] . '"';
                $filtro_expense .= ' AND Expense.date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $filtro_theater .= ' AND Theater.start_date <= "' . $datos['date_to'] . '"';
                $filtro_expense .= ' AND Expense.date <= "' . $datos['date_to'] . '"';
                $fecha_min = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['Theater'] = $filtro_theater;
            $restricciones['Expense'] = $filtro_expense;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_general', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_general')) {

            $restricciones = $this->Session->read('search_conditions_stats_general');
            $filtro_birthday = $restricciones['Job'];
            $filtro_theater = $restricciones['Theater'];
            $filtro_expense = $restricciones['Expense'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';
            $filtro_theater .= ' AND Theater.start_date >= "' . $aux_fecha_min . '"';
            $filtro_expense .= ' AND Expense.date >= "' . $aux_fecha_min . '"';
            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';
            $filtro_theater .= ' AND Theater.start_date <= "' . $aux_fecha_max . '"';
            $filtro_expense .= ' AND Expense.date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //cumpleaños
        $sql_birthday = 'SELECT COUNT(*) as cantidad,
                                SUM(employee_cost) as employee_cost, 
                                SUM(services_cost) as services_cost,
                                SUM(total_cost) as client_cost,
                                SUM(total_cost-services_cost-employee_cost) as festilu_profit
                         FROM jobs as Job
                         WHERE type = "birthday"' . $filtro_birthday;

//        pr($sql_birthday);
//        die;

        $stat_birthday = $this->Stat->query($sql_birthday);
        $this->set('stat_birthday', $stat_birthday);

        //eventos
        $sql_event = 'SELECT COUNT(*) as cantidad,
                             SUM(employee_cost) as employee_cost, 
                             SUM(services_cost) as services_cost,
                             SUM(total_cost) as client_cost,
                             SUM(total_cost-services_cost-employee_cost) as festilu_profit
                      FROM jobs as Job
                      WHERE type = "event"' . $filtro_birthday;
        $stat_event = $this->Stat->query($sql_event);
        $this->set('stat_event', $stat_event);
        
//                pr($sql_event);
//        die;

        //obras de teatro
        $sql_theater = 'SELECT COUNT(*) as cantidad,
                               SUM(employee_cost) as employee_cost,
                               SUM(expense_cost) as expense_cost,
                               SUM(ticket_price * ticket_sales) as client_cost,
                               SUM((ticket_price * ticket_sales) - employee_cost - expense_cost) as festilu_profit
                        FROM theaters as Theater
                        WHERE 1 = 1' . $filtro_theater;
        $stat_theater = $this->Stat->query($sql_theater);
        $this->set('stat_theater', $stat_theater);

        //gastos extra
        $sql_expenses = 'SELECT COUNT(*) as cantidad,
                                SUM(value) as value
                         FROM expenses as Expense
                         WHERE 1 = 1' . $filtro_expense;
        $stat_expenses = $this->Stat->query($sql_expenses);
        $this->set('stat_expenses', $stat_expenses);
    }

    function employee() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $filtro_theater = '';
        $filtro_expense = '';

        $this->Show->recursive = -1;
        $shows = $this->Show->find('list');

        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['employee_id']) && trim($datos['employee_id']) != '') {
                $filtro_birthday .= ' AND JobsDetailsEmployee.employee_id = ' . $datos['employee_id'];
                $filtro_theater .= ' AND TheatersEmployee.employee_id = ' . $datos['employee_id'];
                $filtro_expense .= ' AND Expense.employee_id = ' . $datos['employee_id'];
            }
            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $filtro_theater .= ' AND Theater.start_date >= "' . $datos['date_from'] . '"';
                $filtro_expense .= ' AND Expense.date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $filtro_theater .= ' AND Theater.start_date <= "' . $datos['date_to'] . '"';
                $filtro_expense .= ' AND Expense.date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['Theater'] = $filtro_theater;
            $restricciones['Expense'] = $filtro_expense;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_employee', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_employee')) {

            $restricciones = $this->Session->read('search_conditions_stats_employee');
            $filtro_birthday = $restricciones['Job'];
            $filtro_theater = $restricciones['Theater'];
            $filtro_expense = $restricciones['Expense'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';
            $filtro_theater .= ' AND Theater.start_date >= "' . $aux_fecha_min . '"';
            $filtro_expense .= ' AND Expense.date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';
            $filtro_theater .= ' AND Theater.start_date <= "' . $aux_fecha_max . '"';
            $filtro_expense .= ' AND Expense.date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //si no hay ningun empleado en el filtro, busco el primer empleado
        if (!isset($this->data['Stat']['employee_id'])) {
            $emp = $this->Employee->find('first', array('conditions' => array('Employee.deleted' => 0)));
            $this->data['Stat']['employee_id'] = $emp['Employee']['id'];

            $filtro_birthday .= ' AND JobsDetailsEmployee.employee_id = ' . $emp['Employee']['id'];
            $filtro_theater .= ' AND TheatersEmployee.employee_id = ' . $emp['Employee']['id'];
            $filtro_expense .= ' AND Expense.employee_id = ' . $emp['Employee']['id'];
        }

        $empleados = $this->Employee->find('all', array('fields' => array('Employee.first_name', 'Employee.last_name', 'Employee.id'), 'order' => array('first_name' => 'ASC', 'last_name' => 'ASC')));
        $employees = array();
        foreach ($empleados as $emp) {
            $employees[$emp['Employee']['id']] = $emp['Employee']['first_name'] . ' ' . $emp['Employee']['last_name'];
        }
        $this->set(compact('employees'));

        //cumpleaños
        $sql_birthday = 'SELECT *
                         FROM jobs as Job
                         LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                         LEFT JOIN jobs_details_employees as JobsDetailsEmployee ON JobsDetail.id = JobsDetailsEmployee.jobs_detail_id
                         WHERE Job.type = "birthday"' . $filtro_birthday . ' order by Job.start_date ASC';

        $stat_birthday = $this->Stat->query($sql_birthday);
        $array_birthday = array();
        $tipos_tareas = array('show' => 'Show', 'reception' => 'Presentación', 'animation' => 'Animación', 'production' => 'Obra', 'service' => 'Servicios');
        foreach ($stat_birthday as $birthday) {
            $array_birthday[$birthday['Job']['id']]['job_id'] = $birthday['Job']['id'];
            $array_birthday[$birthday['Job']['id']]['date'] = date('d/m/Y', strtotime($birthday['Job']['start_date']));
            $array_birthday[$birthday['Job']['id']]['address'] = $birthday['Job']['address'];
            if (!isset($array_birthday[$birthday['Job']['id']]['cost'])) {
                $array_birthday[$birthday['Job']['id']]['cost'] = $birthday['JobsDetailsEmployee']['cost'];
            } else {
                $array_birthday[$birthday['Job']['id']]['cost'] += $birthday['JobsDetailsEmployee']['cost'];
            }
            //para ver si esta todo pagado
            if (!isset($array_birthday[$birthday['Job']['id']]['paid'])) {
                $array_birthday[$birthday['Job']['id']]['paid'] = $birthday['JobsDetailsEmployee']['paid'];
            } else {
                //si ya esta seteado pregunto si el nuevo es cero, entonces tomo ese valor
                if ($birthday['JobsDetailsEmployee']['paid'] == 0) {
                    $array_birthday[$birthday['Job']['id']]['paid'] = $birthday['JobsDetailsEmployee']['paid'];
                }
            }

            if ($birthday['JobsDetail']['type'] == 'show') {
                $array_birthday[$birthday['Job']['id']]['details']['show_' . $birthday['JobsDetail']['show_id']] = $shows[$birthday['JobsDetail']['show_id']];
            } else {
                $array_birthday[$birthday['Job']['id']]['details'][$birthday['JobsDetail']['type']] = $tipos_tareas[$birthday['JobsDetail']['type']];
            }
        }
        $this->set('birthdays', $array_birthday);

        //eventos
        $sql_event = 'SELECT *
                      FROM jobs as Job
                      LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                      LEFT JOIN jobs_details_employees as JobsDetailsEmployee ON JobsDetail.id = JobsDetailsEmployee.jobs_detail_id
                      WHERE Job.type = "event"' . $filtro_birthday . ' order by Job.start_date ASC';
        $stat_event = $this->Stat->query($sql_event);

        $array_event = array();
        $tipos_tareas = array('show' => 'Show', 'reception' => 'Presentación', 'animation' => 'Animación', 'production' => 'Obra');
        foreach ($stat_event as $event) {
            $array_event[$event['Job']['id']]['job_id'] = $event['Job']['id'];
            $array_event[$event['Job']['id']]['date'] = date('d/m/Y', strtotime($event['Job']['start_date']));
            $array_event[$event['Job']['id']]['address'] = $event['Job']['address'];
            if (!isset($array_event[$event['Job']['id']]['cost'])) {
                $array_event[$event['Job']['id']]['cost'] = $event['JobsDetailsEmployee']['cost'];
            } else {
                $array_event[$event['Job']['id']]['cost'] += $event['JobsDetailsEmployee']['cost'];
            }
            //para ver si esta todo pagado
            if (!isset($array_event[$event['Job']['id']]['paid'])) {
                $array_event[$event['Job']['id']]['paid'] = $event['JobsDetailsEmployee']['paid'];
            } else {
                //si ya esta seteado pregunto si el nuevo es cero, entonces tomo ese valor
                if ($event['JobsDetailsEmployee']['paid'] == 0) {
                    $array_event[$event['Job']['id']]['paid'] = $event['JobsDetailsEmployee']['paid'];
                }
            }

            if ($event['JobsDetail']['type'] == 'show') {
                $array_event[$event['Job']['id']]['details']['show_' . $event['JobsDetail']['show_id']] = $shows[$event['JobsDetail']['show_id']];
            } else {
                $array_event[$event['Job']['id']]['details'][$event['JobsDetail']['type']] = $tipos_tareas[$event['JobsDetail']['type']];
            }
        }
        $this->set('events', $array_event);

        //obras de teatro
        $sql_theater = 'SELECT *
                        FROM theaters as Theater 
                         LEFT JOIN theaters_employees as TheatersEmployee ON Theater.id = TheatersEmployee.theater_id
                        WHERE 1 = 1' . $filtro_theater . ' order by Theater.start_date ASC';

        $stat_theater = $this->Stat->query($sql_theater);
        $array_theater = array();
        foreach ($stat_theater as $theater) {
            $array_theater[$theater['Theater']['id']]['theater_id'] = $theater['Theater']['id'];
            $array_theater[$theater['Theater']['id']]['date'] = date('d/m/Y', strtotime($theater['Theater']['start_date']));
            $array_theater[$theater['Theater']['id']]['address'] = $theater['Theater']['address'];
            if (!isset($array_theater[$theater['Theater']['id']]['cost'])) {
                $array_theater[$theater['Theater']['id']]['cost'] = $theater['TheatersEmployee']['cost'];
            } else {
                $array_theater[$theater['Theater']['id']]['cost'] += $theater['TheatersEmployee']['cost'];
            }
            //para ver si esta todo pagado
            if (!isset($array_theater[$theater['Theater']['id']]['paid'])) {
                $array_theater[$theater['Theater']['id']]['paid'] = $theater['TheatersEmployee']['paid'];
            } else {
                //si ya esta seteado pregunto si el nuevo es cero, entonces tomo ese valor
                if ($theater['TheatersEmployee']['paid'] == 0) {
                    $array_theater[$theater['Theater']['id']]['paid'] = $theater['TheatersEmployee']['paid'];
                }
            }
        }

        $this->set('theaters', $array_theater);

        //gastos extra
        $sql_expenses = 'SELECT *
                         FROM expenses as Expense
                         WHERE 1 = 1' . $filtro_expense . ' order by Expense.date ASC';
        $stat_expenses = $this->Stat->query($sql_expenses);
        $array_expense = array();
        foreach ($stat_expenses as $expense) {
            $array_expense[$expense['Expense']['id']]['expense_id'] = $expense['Expense']['id'];
            $array_expense[$expense['Expense']['id']]['date'] = date('d/m/Y', strtotime($expense['Expense']['date']));
            $array_expense[$expense['Expense']['id']]['description'] = $expense['Expense']['description'];
            $array_expense[$expense['Expense']['id']]['value'] = $expense['Expense']['value'];
            $array_expense[$expense['Expense']['id']]['paid'] = $expense['Expense']['paid'];
        }

        $this->set('expenses', $array_expense);

        $expensetypes = $this->Expense->Expensetype->find('list');
        $expensetypes = array(null => 'Seleccione') + $expensetypes;
        $this->set(compact('expensetypes'));
        //guardo los datos del empleado para despues tomarlo en la funcion que grafica
        $array_datos = array();
        $array_datos['birthdays'] = $array_birthday;
        $array_datos['events'] = $array_event;
        $array_datos['theaters'] = $array_theater;
        $array_datos['expenses'] = $array_expense;
        $array_datos['filtros'] = $this->data['Stat']; //fechas y employee_id
        $this->Session->write('data_stats_employee', $array_datos);
    }

    /**
     * Función que paga todo lo que hizo un empleado en un determinado rango de fechas
     * @param type $employee_id
     * @param type $date_from
     * @param type $date_to
     */
    function pay_all($employee_id, $date_from, $date_to, $pagar_caja = NULL) {

        //empiezo una transaccion
        $this->Stat->query('SET AUTOCOMMIT = 0;');
        $this->Stat->query('START TRANSACTION;');
        $bandera = true; //la uso para ver si algo salio mal

        if ($pagar_caja) {
            //voy a comprobar que alcance la plata dela caja
            $costo_total = 0;

            //***************************************************
            //calculo total de cumpleaños
            //***************************************************
            $sql = 'SELECT SUM(JobsDetailsEmployee.cost) as total
                FROM jobs_details_employees as JobsDetailsEmployee
                WHERE paid = 0
                AND JobsDetailsEmployee.employee_id = ' . $employee_id . '
                AND JobsDetailsEmployee.jobs_detail_id IN (SELECT JobsDetail.id
                                                             FROM jobs_details as JobsDetail, jobs as Job
                                                             WHERE Job.id = JobsDetail.job_id
                                                              AND Job.type = "birthday"
                                                              AND Job.start_date >= "' . $date_from . '"
                                                              AND Job.finish_date <= "' . $date_to . '"
                                                              )';

            $res = $this->Stat->query($sql);

            if (!empty($res)) {
                $costo_total += $res[0][0]['total'];
            }
            //***************************************************
            //calculo total de eventos
            //***************************************************
            $sql = 'SELECT SUM(JobsDetailsEmployee.cost) as total
                FROM jobs_details_employees as JobsDetailsEmployee
                WHERE paid = 0
                AND JobsDetailsEmployee.employee_id = ' . $employee_id . '
                AND JobsDetailsEmployee.jobs_detail_id IN (SELECT JobsDetail.id
                                                             FROM jobs_details as JobsDetail, jobs as Job
                                                             WHERE Job.id = JobsDetail.job_id
                                                              AND Job.type = "event"
                                                              AND Job.start_date >= "' . $date_from . '"
                                                              AND Job.finish_date <= "' . $date_to . '"
                                                              )';

            $res = $this->Stat->query($sql);

            if (!empty($res)) {
                $costo_total += $res[0][0]['total'];
            }

            //***************************************************
            //calculo total de obras
            //***************************************************
            $sql = 'SELECT SUM(TheatersEmployee.cost) as total
                FROM theaters_employees as TheatersEmployee
                WHERE paid = 0
                AND TheatersEmployee.employee_id = ' . $employee_id . '
                AND TheatersEmployee.theater_id IN (SELECT Theater.id
                                                    FROM theaters as Theater
                                                    WHERE Theater.start_date >= "' . $date_from . '"
                                                     AND Theater.finish_date <= "' . $date_to . '"
                                                              )';

            $res = $this->Stat->query($sql);
            
            if (!empty($res)) {
                $costo_total += $res[0][0]['total'];
            }

            //***************************************************
            //calculo total de gastos
            //***************************************************
            $sql = 'SELECT SUM(value) as total
                FROM expenses as Expense
                WHERE paid = 0
                AND Expense.employee_id = ' . $employee_id . '
                AND Expense.date >= "' . $date_from . '"
                AND Expense.date <= "' . $date_to . '"';

            $res = $this->Stat->query($sql);

            if (!empty($res)) {
                $costo_total += $res[0][0]['total'];
            }

            //controlo que haya suficiente dinero en la caja
            if (!$this->Cash->controlarPago($costo_total)) {
                //si no alcanza el dinero de la caja para pagar 
                //redirecciono al lugar desde donde fue llamado
                $this->Session->setFlash(__('El dinero en la caja no es suficiente para realizar el pago', true));
                $this->redirect($this->referer());
            }
        }

        if (!$this->Job->payAll('birthday', $employee_id, $date_from, $date_to, $pagar_caja)) {
            $bandera = false;
        }
        if (!$this->Job->payAll('event', $employee_id, $date_from, $date_to, $pagar_caja)) {
            $bandera = false;
        }
        if (!$this->Theater->payAll($employee_id, $date_from, $date_to, $pagar_caja)) {
            $bandera = false;
        }
        if (!$this->Expense->payAll($employee_id, $date_from, $date_to, $pagar_caja)) {
            $bandera = false;
        }

        if ($bandera) {
            $this->Session->setFlash('Los Pago han sido registrados');
            $this->Stat->query('COMMIT;');
            $this->Stat->query('SET AUTOCOMMIT = 1;');
        } else {
            $this->Session->setFlash('Los Pago no han sido registrados. Por favor, intente nuevamente.');
            $this->Stat->query('ROLLBACK;');
            $this->Stat->query('SET AUTOCOMMIT = 1;');
        }

        //redirecciono al lugar desde donde fue llamado
        $this->redirect($this->referer());
    }

    function costume() {
        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $filtro_theater = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $filtro_theater .= ' AND Theater.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $filtro_theater .= ' AND Theater.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['Theater'] = $filtro_theater;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_costumes', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_costumes')) {

            $restricciones = $this->Session->read('search_conditions_stats_costumes');
            $filtro_birthday = $restricciones['Job'];
            $filtro_theater = $restricciones['Theater'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';
            $filtro_theater .= ' AND Theater.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';
            $filtro_theater .= ' AND Theater.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //trabajos
        $sql = 'SELECT costume_id, count(JobsDetailsEmployee.costume_id) as cantidad
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                LEFT JOIN jobs_details_employees as JobsDetailsEmployee ON JobsDetail.id = JobsDetailsEmployee.jobs_detail_id
                LEFT JOIN costumes as Costume ON JobsDetailsEmployee.costume_id = Costume.id
                WHERE JobsDetailsEmployee.costume_id IS NOT NULL
                AND Costume.deleted = 0' . $filtro_birthday . ' group by JobsDetailsEmployee.costume_id';

        $res_jobs = $this->Stat->query($sql);
        $array_datos = array();
        foreach ($res_jobs as $job) {
            //$array_datos[$job['JobsDetailsEmployee']['costume_id']]['costume_id'] = $job['JobsDetailsEmployee']['costume_id'];
            $array_datos[$job['JobsDetailsEmployee']['costume_id']] = $job[0]['cantidad'];
        }

        //obras de teatro
        $sql = 'SELECT costume_id, count(TheatersCostume.costume_id) as cantidad
                FROM theaters as Theater
                LEFT JOIN theaters_costumes as TheatersCostume ON Theater.id = TheatersCostume.theater_id
                LEFT JOIN costumes as Costume ON TheatersCostume.costume_id = Costume.id
                WHERE TheatersCostume.costume_id IS NOT NULL
                AND Costume.deleted = 0' . $filtro_theater . ' group by TheatersCostume.costume_id';

        $res_theaters = $this->Stat->query($sql);
        foreach ($res_theaters as $theater) {
            if (isset($array_datos[$theater['TheatersCostume']['costume_id']])) {
                //si ya estaba en el array, le agrego a la cantidad
                $array_datos[$theater['TheatersCostume']['costume_id']] += $theater[0]['cantidad'];
            } else {
                //sino, lo cargo entero
                //$array_datos[$theater['TheatersCostume']['costume_id']]['costume_id'] = $theater['TheatersCostume']['costume_id'];
                $array_datos[$theater['TheatersCostume']['costume_id']] = $theater[0]['cantidad'];
            }
        }
        arsort($array_datos);

        $this->set('array_datos', $array_datos);
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_costumes', $array_datos);
        $this->set('costumes', $this->Costume->find('list'));
    }

    function grafic_costume() {
        $array_datos = $this->Session->read('grafic_stats_costumes');
        $costumes = $this->Costume->find('list');

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");


        /* Create and populate the pData object */
        $MyData = new pData();

        foreach ($array_datos as $costume_id => $cantidad) {
            $MyData->addPoints($cantidad, "Cantidad");
            $MyData->addPoints($costumes[$costume_id], "Trajes");
        }
        $MyData->setAxisName(0, "Cantidad");
        $MyData->setSerieDescription("Trajes", "Trajes");
        $MyData->setAbscissa("Trajes");
        $MyData->setAxisDisplay(0, AXIS_FORMAT_METRIC, 1);


        //seteo el tamaño del gráfico según la cantidad de shows que se van a mostrar
        $cantidad_registros = count($array_datos);

        $alto_picture = 100 + $cantidad_registros * 30;

        /* Create the pChart object */
        $myPicture = new pImage(700, $alto_picture, $MyData);
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8));

        $alto_zona = $alto_picture - 20;
        /* Draw the chart scale */
        $myPicture->setGraphArea(150, 30, 680, $alto_zona);
        $myPicture->drawScale(array("CycleBackground" => TRUE, "DrawSubTicks" => FALSE, "GridR" => 0, "GridG" => 0, "GridB" => 0, "GridAlpha" => 10, "Pos" => SCALE_POS_TOPBOTTOM)); //

        /* Turn on shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Create the per bar palette */
        $Palette = array("0" => array("R" => 188, "G" => 224, "B" => 46, "Alpha" => 100),
            "1" => array("R" => 224, "G" => 100, "B" => 46, "Alpha" => 100),
            "2" => array("R" => 224, "G" => 214, "B" => 46, "Alpha" => 100),
            "3" => array("R" => 46, "G" => 151, "B" => 224, "Alpha" => 100),
            "4" => array("R" => 176, "G" => 46, "B" => 224, "Alpha" => 100),
            "5" => array("R" => 224, "G" => 46, "B" => 117, "Alpha" => 100),
            "6" => array("R" => 92, "G" => 224, "B" => 46, "Alpha" => 100),
            "7" => array("R" => 224, "G" => 176, "B" => 46, "Alpha" => 100));

        /* Draw the chart */
        $myPicture->drawBarChart(array("DisplayPos" => LABEL_POS_INSIDE, "DisplayValues" => TRUE, "Rounded" => TRUE, "Surrounding" => 30, "OverrideColors" => $Palette));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawBarChart.vertical.png");

//
//        /* CAT:Pie charts */
//        /* pChart library inclusions */
//        include(SITE_PATH."/app/vendors/pchart/class/pData.class.php");
//        include(SITE_PATH."/app/vendors/pchart/class/pDraw.class.php");
//        include(SITE_PATH."/app/vendors/pchart/class/pPie.class.php");
//        include(SITE_PATH."/app/vendors/pchart/class/pImage.class.php");
//
//        /* Create and populate the pData object */
//        $MyData = new pData();
//        $labels = array();
//        foreach ($array_datos as $costume_id => $cantidad) {
//            $MyData->addPoints($costumes[$costume_id],"Labels");
//            $MyData->addPoints($cantidad,"ScoreA");
//        }
//        //Fin insercion de datos
//
//        $MyData->setSerieDescription("ScoreA","Application A");
//        $MyData->setAbscissa("Labels");
//
//        /* Create the pChart object */
//        $myPicture = new pImage(650,270,$MyData,TRUE);
//
//        /* Set the default font properties */
//        $myPicture->setFontProperties(array("FontName"=>SITE_PATH."/app/vendors/pchart/fonts/pf_arma_five.ttf","FontSize"=>10,"R"=>10,"G"=>10,"B"=>10));
//
//        /* Create the pPie object */
//        $PieChart = new pPie($myPicture,$MyData);
//
//        /* Write down the legend next to the 2nd chart*/
//        $PieChart->drawPieLegend(550,70);
//
//        /* Enable shadow computing */
//        $myPicture->setShadow(TRUE,array("X"=>3,"Y"=>3,"R"=>0,"G"=>0,"B"=>0,"Alpha"=>10));
//
//        /* Draw a splitted pie chart */
////         $PieChart->draw3DPie(180,135,array("Radius"=>160,"DataGapAngle"=>12,"DataGapRadius"=>10,"Border"=>TRUE));
//
//        /* Draw an AA pie chart */
//        $PieChart->draw3DPie(280,140,array("WriteValues"=>FALSE, "ValueR" => 10,"ValueG" => 10,"ValueB" => 10,"Radius"=>150,"DrawLabels"=>TRUE,"LabelStacked"=>TRUE,"Border"=>TRUE));
//
//
//        $myPicture->autoOutput("pictures/example.draw2DPie.labels.png");
    }

    function show() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $filtro_theater = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_shows', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_shows')) {

            $restricciones = $this->Session->read('search_conditions_stats_shows');
            $filtro_birthday = $restricciones['Job'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //trabajos
        $sql = 'SELECT show_id, count(JobsDetail.show_id) as cantidad, SUM(JobsDetail.cost) as costo_cliente, SUM(JobsDetail.employee_cost) as costo_empleados
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                LEFT JOIN shows as Shows ON JobsDetail.show_id = Shows.id
                WHERE JobsDetail.type = "show" ' . $filtro_birthday . ' group by JobsDetail.show_id order by cantidad DESC';

        $res_shows = $this->Stat->query($sql);
//        pr($res_shows);
        $array_datos = array();
        foreach ($res_shows as $job) {
            $array_datos[$job['JobsDetail']['show_id']]['show_id'] = $job['JobsDetail']['show_id'];
            $array_datos[$job['JobsDetail']['show_id']]['cantidad'] = $job[0]['cantidad'];
            $array_datos[$job['JobsDetail']['show_id']]['costo_cliente'] = $job[0]['costo_cliente'];
            $array_datos[$job['JobsDetail']['show_id']]['costo_empleados'] = $job[0]['costo_empleados'];
        }

        $this->set('array_datos', $array_datos);
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_shows', $array_datos);
        $this->set('shows', $this->Show->find('list'));
    }

    function grafic_show() {
        $array_datos = $this->Session->read('grafic_stats_shows');
        $shows = $this->Show->find('list');

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");


        /* Create and populate the pData object */
        $MyData = new pData();

        foreach ($array_datos as $dato) {
            $MyData->addPoints($dato['cantidad'], "Cantidad");
            $MyData->addPoints($shows[$dato['show_id']], "Shows");
        }
        $MyData->setAxisName(0, "Cantidad");
        $MyData->setSerieDescription("Shows", "Shows");
        $MyData->setAbscissa("Shows");
//        $MyData->setAbscissaName("Shows");
        $MyData->setAxisDisplay(0, AXIS_FORMAT_METRIC, 1);


        //seteo el tamaño del gráfico según la cantidad de shows que se van a mostrar
        $cantidad_registros = count($array_datos);

        $alto_picture = 100 + $cantidad_registros * 30;

        /* Create the pChart object */
        $myPicture = new pImage(700, $alto_picture, $MyData);
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8));

        $alto_zona = $alto_picture - 20;
        /* Draw the chart scale */
        $myPicture->setGraphArea(150, 30, 680, $alto_zona);
        $myPicture->drawScale(array("CycleBackground" => TRUE, "DrawSubTicks" => FALSE, "GridR" => 0, "GridG" => 0, "GridB" => 0, "GridAlpha" => 10, "Pos" => SCALE_POS_TOPBOTTOM)); //

        /* Turn on shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Create the per bar palette */
        $Palette = array("0" => array("R" => 188, "G" => 224, "B" => 46, "Alpha" => 100),
            "1" => array("R" => 224, "G" => 100, "B" => 46, "Alpha" => 100),
            "2" => array("R" => 224, "G" => 214, "B" => 46, "Alpha" => 100),
            "3" => array("R" => 46, "G" => 151, "B" => 224, "Alpha" => 100),
            "4" => array("R" => 176, "G" => 46, "B" => 224, "Alpha" => 100),
            "5" => array("R" => 224, "G" => 46, "B" => 117, "Alpha" => 100),
            "6" => array("R" => 92, "G" => 224, "B" => 46, "Alpha" => 100),
            "7" => array("R" => 224, "G" => 176, "B" => 46, "Alpha" => 100));

        /* Draw the chart */
        $myPicture->drawBarChart(array("DisplayPos" => LABEL_POS_INSIDE, "DisplayValues" => TRUE, "Rounded" => TRUE, "Surrounding" => 30, "OverrideColors" => $Palette));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawBarChart.vertical.png");
    }

    function pelotero() {
        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_pelotero', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_pelotero')) {

            $restricciones = $this->Session->read('search_conditions_stats_pelotero');
            $filtro_birthday = $restricciones['Job'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }
        //trabajos
//        $sql = 'SELECT pelotero_id, count(Job.pelotero_id) as cantidad, SUM(JobsDetail.cost) as costo_cliente, SUM(JobsDetail.employee_cost) as costo_empleados
//                FROM jobs as Job
//                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
//                WHERE Job.pelotero_id is not NULL ' . $filtro_birthday . ' 
//                    group by Job.pelotero_id order by cantidad DESC';
        //trabajos
        $sql = 'SELECT pelotero_id, count(distinct(Job.id)) as cantidad, SUM(JobsDetail.cost) as costo_cliente, SUM(JobsDetail.employee_cost) as costo_empleados
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                WHERE Job.pelotero_id is not NULL ' . $filtro_birthday . ' 
                    group by Job.pelotero_id order by cantidad DESC';
        
        $res_peloteros = $this->Stat->query($sql);

        $array_datos = array();
        foreach ($res_peloteros as $pelo) {
            $array_datos[$pelo['Job']['pelotero_id']]['pelotero_id'] = $pelo['Job']['pelotero_id'];
            $array_datos[$pelo['Job']['pelotero_id']]['cantidad'] = $pelo[0]['cantidad'];
            $array_datos[$pelo['Job']['pelotero_id']]['costo_cliente'] = $pelo[0]['costo_cliente'];
            $array_datos[$pelo['Job']['pelotero_id']]['costo_empleados'] = $pelo[0]['costo_empleados'];
        }

        $this->set('array_datos', $array_datos);
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_pelotero', $array_datos);
        $this->set('peloteros', $this->Pelotero->find('list'));
    }

    function grafic_pelotero() {
        $array_datos = $this->Session->read('grafic_stats_pelotero');
        $peloteros = $this->Pelotero->find('list');

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");


        /* Create and populate the pData object */
        $MyData = new pData();

        foreach ($array_datos as $dato) {
            $MyData->addPoints($dato['cantidad'], "Cantidad");
            $MyData->addPoints($peloteros[$dato['pelotero_id']], "Peloteros");
        }
        $MyData->setAxisName(0, "Cantidad");
        $MyData->setSerieDescription("Peloteros", "Peloteros");
        $MyData->setAbscissa("Peloteros");

        $MyData->setAxisDisplay(0, AXIS_FORMAT_METRIC, 1);


        //seteo el tamaño del gráfico según la cantidad de shows que se van a mostrar
        $cantidad_registros = count($array_datos);

        $alto_picture = 100 + $cantidad_registros * 30;

        /* Create the pChart object */
        $myPicture = new pImage(700, $alto_picture, $MyData);
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8));

        $alto_zona = $alto_picture - 20;
        /* Draw the chart scale */
        $myPicture->setGraphArea(150, 30, 680, $alto_zona);
        $myPicture->drawScale(array("CycleBackground" => TRUE, "DrawSubTicks" => FALSE, "GridR" => 0, "GridG" => 0, "GridB" => 0, "GridAlpha" => 10, "Pos" => SCALE_POS_TOPBOTTOM)); //

        /* Turn on shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Create the per bar palette */
        $Palette = array("0" => array("R" => 188, "G" => 224, "B" => 46, "Alpha" => 100),
            "1" => array("R" => 224, "G" => 100, "B" => 46, "Alpha" => 100),
            "2" => array("R" => 224, "G" => 214, "B" => 46, "Alpha" => 100),
            "3" => array("R" => 46, "G" => 151, "B" => 224, "Alpha" => 100),
            "4" => array("R" => 176, "G" => 46, "B" => 224, "Alpha" => 100),
            "5" => array("R" => 224, "G" => 46, "B" => 117, "Alpha" => 100),
            "6" => array("R" => 92, "G" => 224, "B" => 46, "Alpha" => 100),
            "7" => array("R" => 224, "G" => 176, "B" => 46, "Alpha" => 100));

        /* Draw the chart */
        $myPicture->drawBarChart(array("DisplayPos" => LABEL_POS_INSIDE, "DisplayValues" => TRUE, "Rounded" => TRUE, "Surrounding" => 30, "OverrideColors" => $Palette));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawBarChart.vertical2.png");
    }

    function provider() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_jobs = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['provider_id']) && trim($datos['provider_id']) != '') {
                $filtro_jobs .= ' AND JobsDetail.provider_id = ' . $datos['provider_id'];
            }
            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_jobs .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_jobs .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_jobs;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_provider', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_provider')) {

            $restricciones = $this->Session->read('search_conditions_stats_provider');
            $filtro_jobs = $restricciones['Job'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_jobs .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_jobs .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //si no hay ningun proveedor en el filtro, busco el primer proveedor
        if (!isset($this->data['Stat']['provider_id'])) {
            $pro = $this->Provider->find('first', array('conditions' => array('Provider.deleted' => 0)));
            $this->data['Stat']['provider_id'] = $pro['Provider']['id'];

            $filtro_jobs .= ' AND JobsDetail.provider_id = ' . $pro['Provider']['id'];
        }

        $providers = $this->Provider->find('list', array('conditions' => array('Provider.deleted' => 0), 'order' => array('name' => 'ASC')));
        $this->set(compact('providers'));

        //trabajos
        $sql_jobs = 'SELECT *
                         FROM jobs as Job
                         LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                         WHERE JobsDetail.type = "service"' . $filtro_jobs . ' order by Job.start_date ASC';


        $stat_jobs = $this->Stat->query($sql_jobs);

        $services = $this->Service->find('list');

        $array_birthday = array();
        foreach ($stat_jobs as $birthday) {
            $array_birthday[$birthday['Job']['id']]['job_id'] = $birthday['Job']['id'];
            $array_birthday[$birthday['Job']['id']]['date'] = date('d/m/Y', strtotime($birthday['Job']['start_date']));
            $array_birthday[$birthday['Job']['id']]['address'] = $birthday['Job']['address'];
            if (!isset($array_birthday[$birthday['Job']['id']]['price'])) {
                $array_birthday[$birthday['Job']['id']]['price'] = $birthday['JobsDetail']['price'];
            } else {
                $array_birthday[$birthday['Job']['id']]['price'] += $birthday['JobsDetail']['price'];
            }
            //para ver si esta todo pagado
            if (!isset($array_birthday[$birthday['Job']['id']]['provider_paid'])) {
                $array_birthday[$birthday['Job']['id']]['paid'] = $birthday['JobsDetail']['provider_paid'];
            } else {
                //si ya esta seteado pregunto si el nuevo es cero, entonces tomo ese valor
                //para dejarlo marcadao como que no fue pagado
                if ($birthday['JobsDetailsEmployee']['provider_paid'] == 0) {
                    $array_birthday[$birthday['Job']['id']]['paid'] = $birthday['JobsDetail']['provider_paid'];
                }
            }
            $array_birthday[$birthday['Job']['id']]['details'][$birthday['JobsDetail']['service_id']] = $services[$birthday['JobsDetail']['service_id']];
        }

//        pr($array_birthday);
//        die;
        $this->set('birthdays', $array_birthday);

        $services = array(null => 'Seleccione') + $services;
        $this->set(compact('services'));
    }

    function pelotero_show() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['pelotero_id']) && trim($datos['pelotero_id']) != '') {
                $filtro_birthday .= ' AND Job.pelotero_id = ' . $datos['pelotero_id'];
            }

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_pelotero_show', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_pelotero_show')) {

            $restricciones = $this->Session->read('search_conditions_stats_pelotero_show');
            $filtro_birthday = $restricciones['Job'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //si no hay ningun pelotero en el filtro, busco el primer pelotero
        if (!isset($this->data['Stat']['pelotero_id'])) {
            $pel = $this->Pelotero->find('first', array('conditions' => array('Pelotero.deleted' => 0)));
            $this->data['Stat']['pelotero_id'] = $pel['Pelotero']['id'];

            $filtro_birthday .= ' AND Job.pelotero_id = ' . $pel['Pelotero']['id'];
        }

        $peloteros = $this->Pelotero->find('list', array('conditions' => array('Pelotero.deleted' => 0), 'order' => array('name' => 'ASC')));
        $this->set(compact('peloteros'));

        //trabajos
        $sql = 'SELECT show_id, count(JobsDetail.show_id) as cantidad, SUM(JobsDetail.cost) as costo_cliente, SUM(JobsDetail.employee_cost) as costo_empleados
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                LEFT JOIN shows as Shows ON JobsDetail.show_id = Shows.id
                WHERE JobsDetail.type = "show" AND Job.pelotero_id is not NULL' . $filtro_birthday . ' 
                    group by JobsDetail.show_id order by cantidad DESC';

        $res_shows = $this->Stat->query($sql);
//        pr($res_shows);
        $array_datos = array();
        foreach ($res_shows as $job) {
            $array_datos[$job['JobsDetail']['show_id']]['show_id'] = $job['JobsDetail']['show_id'];
            $array_datos[$job['JobsDetail']['show_id']]['cantidad'] = $job[0]['cantidad'];
            $array_datos[$job['JobsDetail']['show_id']]['costo_cliente'] = $job[0]['costo_cliente'];
            $array_datos[$job['JobsDetail']['show_id']]['costo_empleados'] = $job[0]['costo_empleados'];
        }

        $this->set('array_datos', $array_datos);
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_pelotero_show', $array_datos);
        $this->set('shows', $this->Show->find('list'));
    }

    function grafic_pelotero_show() {
        $array_datos = $this->Session->read('grafic_stats_pelotero_show');
        $shows = $this->Show->find('list');

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");


        /* Create and populate the pData object */
        $MyData = new pData();

        foreach ($array_datos as $dato) {
            $MyData->addPoints($dato['cantidad'], "Cantidad");
            $MyData->addPoints($shows[$dato['show_id']], "Shows");
        }
        $MyData->setAxisName(0, "Cantidad");
        $MyData->setSerieDescription("Shows", "Shows");
        $MyData->setAbscissa("Shows");
//        $MyData->setAbscissaName("Shows");
        $MyData->setAxisDisplay(0, AXIS_FORMAT_METRIC, 1);


        //seteo el tamaño del gráfico según la cantidad de shows que se van a mostrar
        $cantidad_registros = count($array_datos);

        $alto_picture = 100 + $cantidad_registros * 30;

        /* Create the pChart object */
        $myPicture = new pImage(700, $alto_picture, $MyData);
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8));

        $alto_zona = $alto_picture - 20;
        /* Draw the chart scale */
        $myPicture->setGraphArea(150, 30, 680, $alto_zona);
        $myPicture->drawScale(array("CycleBackground" => TRUE, "DrawSubTicks" => FALSE, "GridR" => 0, "GridG" => 0, "GridB" => 0, "GridAlpha" => 10, "Pos" => SCALE_POS_TOPBOTTOM)); //

        /* Turn on shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Create the per bar palette */
        $Palette = array("0" => array("R" => 188, "G" => 224, "B" => 46, "Alpha" => 100),
            "1" => array("R" => 224, "G" => 100, "B" => 46, "Alpha" => 100),
            "2" => array("R" => 224, "G" => 214, "B" => 46, "Alpha" => 100),
            "3" => array("R" => 46, "G" => 151, "B" => 224, "Alpha" => 100),
            "4" => array("R" => 176, "G" => 46, "B" => 224, "Alpha" => 100),
            "5" => array("R" => 224, "G" => 46, "B" => 117, "Alpha" => 100),
            "6" => array("R" => 92, "G" => 224, "B" => 46, "Alpha" => 100),
            "7" => array("R" => 224, "G" => 176, "B" => 46, "Alpha" => 100));

        /* Draw the chart */
        $myPicture->drawBarChart(array("DisplayPos" => LABEL_POS_INSIDE, "DisplayValues" => TRUE, "Rounded" => TRUE, "Surrounding" => 30, "OverrideColors" => $Palette));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawBarChart.vertical.png");
    }

    function show_month() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $valor_mostrar = 'cantidad';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            if (isset($datos['valor_mostrar']) && trim($datos['valor_mostrar']) != '') {
                $valor_mostrar = $datos['valor_mostrar'];
            }

            if (isset($datos['pelotero_particular']) && trim($datos['pelotero_particular']) != '') {
                if ($datos['pelotero_particular'] == 'pelotero') {
                    $filtro_birthday .= ' AND Job.pelotero_id is not NULL';
                } else {
                    $filtro_birthday .= ' AND Job.pelotero_id is NULL';
                }
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['valor_mostrar'] = $valor_mostrar;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_shows_month', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_shows_month')) {

            $restricciones = $this->Session->read('search_conditions_stats_shows_month');
            $filtro_birthday = $restricciones['Job'];
            $valor_mostrar = $restricciones['valor_mostrar'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        if (!isset($this->data['Stat']['valor_mostrar'])) {
            $this->data['Stat']['valor_mostrar'] = $valor_mostrar;
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3month', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+1month', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //trabajos
        $sql = 'SELECT show_id, count(JobsDetail.show_id) as cantidad, 
                        SUM(JobsDetail.cost) as costo_cliente, 
                        SUM(JobsDetail.employee_cost) as costo_empleados,
                        YEAR(Job.start_date) as anio,
                        LPAD(MONTH(Job.start_date), 2, 0) as mes
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                LEFT JOIN shows as Shows ON JobsDetail.show_id = Shows.id
                WHERE JobsDetail.type = "show" ' . $filtro_birthday
                . ' group by JobsDetail.show_id,
                             YEAR(Job.start_date),
                             MONTH(Job.start_date)
                    order by cantidad DESC';

        $res_shows = $this->Stat->query($sql);

        $array_datos = array();
        $fechas_usadas = array();
        foreach ($res_shows as $job) {
            $array_datos[$job['JobsDetail']['show_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['show_id'] = $job['JobsDetail']['show_id'];
            $array_datos[$job['JobsDetail']['show_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['cantidad'] = $job[0]['cantidad'];
            $array_datos[$job['JobsDetail']['show_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['costo_cliente'] = $job[0]['costo_cliente'];
            $array_datos[$job['JobsDetail']['show_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['costo_empleados'] = $job[0]['costo_empleados'];

            $fechas_usadas[$job[0]['anio'] . '-' . $job[0]['mes']] = $job[0]['anio'] . '-' . $job[0]['mes'];
        }

        //ordenamos las fechas utilizadas
        asort($fechas_usadas);

        $this->set('array_datos', $array_datos);
        $this->set('fechas_usadas', $fechas_usadas);

        $datos_grafico = array();
        $datos_grafico['datos'] = $array_datos;
        $datos_grafico['fechas_usadas'] = $fechas_usadas;
        $datos_grafico['valor_mostrar'] = $this->data['Stat']['valor_mostrar'];
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_shows_month', $datos_grafico);
        $this->set('shows', $this->Show->find('list'));
    }

    function grafic_show_month($show_id_mostrar) {

        $info = $this->Session->read('grafic_stats_shows_month');
        $array_datos = $info['datos'];
        $valor_mostrar = $info['valor_mostrar'];
        $fechas_usadas = $info['fechas_usadas'];
        $shows = $this->Show->find('list');

        $tipos_valores = array('cantidad' => __('Cantidad', true), 'costo_empleados' => __('Costo Empleados', true), 'costo_cliente' => __('Costo Cliente', true), 'ganancia' => __('Ganancia', true));

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        //include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");

        $array_valores = array();
        foreach ($array_datos as $show_id => $show) {
            //si es el show que yo quiero mostrar
            if ($show_id_mostrar == $show_id) {

                foreach ($fechas_usadas as $fecha) {
                    $valor_elegido = 0;
                    if (isset($show[$fecha])) {
                        $dato = $show[$fecha];

                        if (isset($dato[$valor_mostrar])) {
                            $valor_elegido = $dato[$valor_mostrar];
                        } else {
                            //si es la ganancia
                            $valor_elegido = ($dato['costo_cliente'] - $dato['costo_empleados']);
                        }
                    }
                    $array_valores[] = $valor_elegido;
                }
                break;
            }
        }


        /* Create and populate the pData object */
        $MyData = new pData();
        $MyData->addPoints($array_valores, $shows[$show_id]);
        $MyData->setSerieWeight($shows[$show_id], 2);
        $MyData->setAxisName(0, $tipos_valores[$valor_mostrar]);
        $MyData->addPoints($fechas_usadas, "Meses");
        $MyData->setSerieDescription("Meses", "Meses");
        $MyData->setAbscissa("Meses");

        /* Create the pChart object */
        $myPicture = new pImage(700, 230, $MyData);
        /* Turn of Antialiasing */
        $myPicture->Antialias = FALSE;

        /* Draw the background */
        $Settings = array("R" => 255, "G" => 255, "B" => 255, "Dash" => 1, "DashR" => 255, "DashG" => 255, "DashB" => 255);
        $myPicture->drawFilledRectangle(0, 0, 700, 230, $Settings);

        /* Write the chart title */
        //$myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8, "R" => 255, "G" => 255, "B" => 255));
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8, "R" => 0, "G" => 0, "B" => 0));
        $myPicture->drawText(10, 16, $shows[$show_id], array("FontSize" => 11, "Align" => TEXT_ALIGN_BOTTOMLEFT));

        /* Set the default font */
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 6, "R" => 0, "G" => 0, "B" => 0));

        /* Define the chart area */
        $myPicture->setGraphArea(60, 40, 650, 200);

        /* Draw the scale */
        $scaleSettings = array("XMargin" => 10, "YMargin" => 10, "Floating" => TRUE, "GridR" => 200, "GridG" => 200, "GridB" => 200, "DrawSubTicks" => TRUE, "CycleBackground" => TRUE);
        $myPicture->drawScale($scaleSettings);

        /* Turn on Antialiasing */
        $myPicture->Antialias = TRUE;

        /* Enable shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Draw the line chart */
        $myPicture->drawLineChart();
        $myPicture->drawPlotChart(array("DisplayValues" => TRUE, "PlotBorder" => TRUE, "BorderSize" => 2, "Surrounding" => -60, "BorderAlpha" => 80));

        /* Write the chart legend */
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 255, "FontG" => 255, "FontB" => 255));
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 0, "FontG" => 0, "FontB" => 0));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawLineChart.plots.png");
    }

    function pelotero_month() {
        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $valor_mostrar = 'cantidad';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }
            if (isset($datos['valor_mostrar']) && trim($datos['valor_mostrar']) != '') {
                $valor_mostrar = $datos['valor_mostrar'];
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['valor_mostrar'] = $valor_mostrar;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_pelotero_month', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_pelotero_month')) {

            $restricciones = $this->Session->read('search_conditions_stats_pelotero_month');
            $filtro_birthday = $restricciones['Job'];
            $valor_mostrar = $restricciones['valor_mostrar'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        if (!isset($this->data['Stat']['valor_mostrar'])) {
            $this->data['Stat']['valor_mostrar'] = $valor_mostrar;
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3day', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+3day', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }
        //trabajos
        $sql = 'SELECT pelotero_id, 
                        count(Job.pelotero_id) as cantidad, 
                        SUM(JobsDetail.cost) as costo_cliente, 
                        SUM(JobsDetail.employee_cost) as costo_empleados,
                        YEAR(Job.start_date) as anio,
                        LPAD(MONTH(Job.start_date), 2, 0) as mes
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                WHERE Job.pelotero_id is not NULL ' . $filtro_birthday . ' 
                GROUP BY Job.pelotero_id ,
                    YEAR(Job.start_date),
                    MONTH(Job.start_date)
                ORDER BY cantidad DESC';

        $res_peloteros = $this->Stat->query($sql);
//        pr($res_peloteros);
//        die;
//        
//        pr($res_shows);
        $array_datos = array();
        $fechas_usadas = array();
        foreach ($res_peloteros as $pelo) {
            $array_datos[$pelo['Job']['pelotero_id']][$pelo[0]['anio'] . '-' . $pelo[0]['mes']]['pelotero_id'] = $pelo['Job']['pelotero_id'];
            $array_datos[$pelo['Job']['pelotero_id']][$pelo[0]['anio'] . '-' . $pelo[0]['mes']]['cantidad'] = $pelo[0]['cantidad'];
            $array_datos[$pelo['Job']['pelotero_id']][$pelo[0]['anio'] . '-' . $pelo[0]['mes']]['costo_cliente'] = $pelo[0]['costo_cliente'];
            $array_datos[$pelo['Job']['pelotero_id']][$pelo[0]['anio'] . '-' . $pelo[0]['mes']]['costo_empleados'] = $pelo[0]['costo_empleados'];

            $fechas_usadas[$pelo[0]['anio'] . '-' . $pelo[0]['mes']] = $pelo[0]['anio'] . '-' . $pelo[0]['mes'];
        }

        //ordenamos las fechas utilizadas
        asort($fechas_usadas);

        $this->set('array_datos', $array_datos);
        $this->set('fechas_usadas', $fechas_usadas);

        $datos_grafico = array();
        $datos_grafico['datos'] = $array_datos;
        $datos_grafico['fechas_usadas'] = $fechas_usadas;
        $datos_grafico['valor_mostrar'] = $this->data['Stat']['valor_mostrar'];

        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_pelotero_month', $datos_grafico);
        $this->set('peloteros', $this->Pelotero->find('list'));
    }

    function grafic_pelotero_month($pelotero_id_mostrar) {

        $info = $this->Session->read('grafic_stats_pelotero_month');
        $array_datos = $info['datos'];
        $valor_mostrar = $info['valor_mostrar'];
        $fechas_usadas = $info['fechas_usadas'];
        $peloteros = $this->Pelotero->find('list');

        $tipos_valores = array('cantidad' => __('Cantidad', true), 'costo_empleados' => __('Costo Empleados', true), 'costo_cliente' => __('Costo Cliente', true), 'ganancia' => __('Ganancia', true));

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        //include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");

        $array_valores = array();
        foreach ($array_datos as $pelotero_id => $pelotero) {
            //si es el show que yo quiero mostrar
            if ($pelotero_id_mostrar == $pelotero_id) {

                foreach ($fechas_usadas as $fecha) {
                    $valor_elegido = 0;
                    if (isset($pelotero[$fecha])) {
                        $dato = $pelotero[$fecha];

                        if (isset($dato[$valor_mostrar])) {
                            $valor_elegido = $dato[$valor_mostrar];
                        } else {
                            //si es la ganancia
                            $valor_elegido = ($dato['costo_cliente'] - $dato['costo_empleados']);
                        }
                    }
                    $array_valores[] = $valor_elegido;
                }
                break;
            }
        }


        /* Create and populate the pData object */
        $MyData = new pData();
        $MyData->addPoints($array_valores, $peloteros[$pelotero_id]);
        $MyData->setSerieWeight($peloteros[$pelotero_id], 2);
        $MyData->setAxisName(0, $tipos_valores[$valor_mostrar]);
        $MyData->addPoints($fechas_usadas, "Meses");
        $MyData->setSerieDescription("Meses", "Meses");
        $MyData->setAbscissa("Meses");

        /* Create the pChart object */
        $myPicture = new pImage(700, 230, $MyData);
        /* Turn of Antialiasing */
        $myPicture->Antialias = FALSE;

        /* Draw the background */
        //$Settings = array("R" => 170, "G" => 183, "B" => 87, "Dash" => 1, "DashR" => 190, "DashG" => 203, "DashB" => 107);
        $Settings = array("R" => 255, "G" => 255, "B" => 255, "Dash" => 1, "DashR" => 255, "DashG" => 255, "DashB" => 255);
        $myPicture->drawFilledRectangle(0, 0, 700, 230, $Settings);

        /* Write the chart title */
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8, "R" => 0, "G" => 0, "B" => 0));
        $myPicture->drawText(10, 16, $peloteros[$pelotero_id_mostrar], array("FontSize" => 11, "Align" => TEXT_ALIGN_BOTTOMLEFT));

        /* Set the default font */
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 6, "R" => 0, "G" => 0, "B" => 0));

        /* Define the chart area */
        $myPicture->setGraphArea(60, 40, 650, 200);

        /* Draw the scale */
        $scaleSettings = array("XMargin" => 10, "YMargin" => 10, "Floating" => TRUE, "GridR" => 200, "GridG" => 200, "GridB" => 200, "DrawSubTicks" => TRUE, "CycleBackground" => TRUE);
        $myPicture->drawScale($scaleSettings);

        /* Turn on Antialiasing */
        $myPicture->Antialias = TRUE;

        /* Enable shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Draw the line chart */
        $myPicture->drawLineChart();
        $myPicture->drawPlotChart(array("DisplayValues" => TRUE, "PlotBorder" => TRUE, "BorderSize" => 2, "Surrounding" => -60, "BorderAlpha" => 80));

        /* Write the chart legend */
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 255, "FontG" => 255, "FontB" => 255));
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 0, "FontG" => 0, "FontB" => 0));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawLineChart.plots.png");
    }

    function service_month() {

        $fecha_min = '';
        $fecha_max = '';
        $filtro_birthday = '';
        $valor_mostrar = 'cantidad';
        //si vienen filtros aplicados
        if (!empty($this->data)) {

            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {

                list($d, $m, $a) = explode("/", $datos['date_from']);
                $datos['date_from'] = $a . "-" . $m . "-" . $d . ' 00:00:00';

                $filtro_birthday .= ' AND Job.start_date >= "' . $datos['date_from'] . '"';
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                list($d, $m, $a) = explode("/", $datos['date_to']);
                $datos['date_to'] = $a . "-" . $m . "-" . $d . ' 23:59:59';

                $filtro_birthday .= ' AND Job.start_date <= "' . $datos['date_to'] . '"';
                $fecha_max = 'si';
            }

            if (isset($datos['valor_mostrar']) && trim($datos['valor_mostrar']) != '') {
                $valor_mostrar = $datos['valor_mostrar'];
            }

            if (isset($datos['pelotero_particular']) && trim($datos['pelotero_particular']) != '') {
                if ($datos['pelotero_particular'] == 'pelotero') {
                    $filtro_birthday .= ' AND Job.pelotero_id is not NULL';
                } else {
                    $filtro_birthday .= ' AND Job.pelotero_id is NULL';
                }
            }

            $restricciones['Job'] = $filtro_birthday;
            $restricciones['valor_mostrar'] = $valor_mostrar;
            $restricciones['data'] = $this->data;
            $this->Session->write('search_conditions_stats_services_month', $restricciones);
        } elseif ($this->Session->check('search_conditions_stats_services_month')) {

            $restricciones = $this->Session->read('search_conditions_stats_services_month');
            $filtro_birthday = $restricciones['Job'];
            $valor_mostrar = $restricciones['valor_mostrar'];
            $this->data = $restricciones['data'];
            $datos = $this->data['Stat'];

            if (isset($datos['date_from']) && trim($datos['date_from']) != '') {
                $fecha_min = 'si';
            }
            if (isset($datos['date_to']) && trim($datos['date_to']) != '') {
                $fecha_max = 'si';
            }
        }

        if (!isset($this->data['Stat']['valor_mostrar'])) {
            $this->data['Stat']['valor_mostrar'] = $valor_mostrar;
        }

        //si no viene ningun filtro de fecha, le pongo lo de la ultima semana
        if ($fecha_min != 'si' && $fecha_max != 'si') {
            $aux_fecha_min = date('Y-m-d 00:00:00', strtotime('-3month', strtotime(date('Y-m-d H:i:s'))));
            $aux_fecha_max = date('Y-m-d 23:59:59', strtotime('+1month', strtotime(date('Y-m-d H:i:s'))));

            $filtro_birthday .= ' AND Job.start_date >= "' . $aux_fecha_min . '"';

            $filtro_birthday .= ' AND Job.start_date <= "' . $aux_fecha_max . '"';

            $this->data['Stat']['date_from'] = date('d/m/Y', strtotime($aux_fecha_min));
            $this->data['Stat']['date_to'] = date('d/m/Y', strtotime($aux_fecha_max));
        }

        //trabajos
        $sql = 'SELECT service_id, 
                        count(JobsDetail.service_id) as cantidad, 
                        SUM(JobsDetail.cost) as costo_cliente, 
                        SUM(JobsDetail.employee_cost) as costo_empleados,
                        SUM(JobsDetail.price) as costo_proveedor,
                        YEAR(Job.start_date) as anio,
                        LPAD(MONTH(Job.start_date), 2, 0) as mes
                FROM jobs as Job
                LEFT JOIN jobs_details as JobsDetail ON Job.id = JobsDetail.job_id
                LEFT JOIN services as Services ON JobsDetail.service_id = Services.id
                WHERE JobsDetail.type = "service" ' . $filtro_birthday
                . ' group by JobsDetail.service_id,
                             YEAR(Job.start_date),
                             MONTH(Job.start_date)
                    order by cantidad DESC';

        $res_services = $this->Stat->query($sql);

        $array_datos = array();
        $fechas_usadas = array();
        foreach ($res_services as $job) {
            $array_datos[$job['JobsDetail']['service_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['service_id'] = $job['JobsDetail']['service_id'];
            $array_datos[$job['JobsDetail']['service_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['cantidad'] = $job[0]['cantidad'];
            $array_datos[$job['JobsDetail']['service_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['costo_cliente'] = $job[0]['costo_cliente'];
            $array_datos[$job['JobsDetail']['service_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['costo_empleados'] = $job[0]['costo_empleados'];
            $array_datos[$job['JobsDetail']['service_id']][$job[0]['anio'] . '-' . $job[0]['mes']]['costo_proveedor'] = $job[0]['costo_proveedor'];

            $fechas_usadas[$job[0]['anio'] . '-' . $job[0]['mes']] = $job[0]['anio'] . '-' . $job[0]['mes'];
        }

        //ordenamos las fechas utilizadas
        asort($fechas_usadas);

        $this->set('array_datos', $array_datos);
        $this->set('fechas_usadas', $fechas_usadas);

        $datos_grafico = array();
        $datos_grafico['datos'] = $array_datos;
        $datos_grafico['fechas_usadas'] = $fechas_usadas;
        $datos_grafico['valor_mostrar'] = $this->data['Stat']['valor_mostrar'];
        //guardo los datos de los trajes en la session para despues tomarlo en la funcion que grafica
        $this->Session->write('grafic_stats_services_month', $datos_grafico);
        $this->set('services', $this->Service->find('list'));
    }

    function grafic_service_month($service_id_mostrar) {

        $info = $this->Session->read('grafic_stats_services_month');
        $array_datos = $info['datos'];
        $valor_mostrar = $info['valor_mostrar'];
        $fechas_usadas = $info['fechas_usadas'];
        $services = $this->Service->find('list');

        $tipos_valores = array('cantidad' => __('Cantidad', true), 'costo_empleados' => __('Costo Empleados', true), 'costo_cliente' => __('Costo Cliente', true), 'costo_proveedor' => __('Costo Proveedor', true), 'ganancia' => __('Ganancia', true));

        /* CAT:Pie charts */
        /* pChart library inclusions */
        include(SITE_PATH . "/app/vendors/pchart/class/pData.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pDraw.class.php");
        //include(SITE_PATH . "/app/vendors/pchart/class/pPie.class.php");
        include(SITE_PATH . "/app/vendors/pchart/class/pImage.class.php");

        $array_valores = array();
        foreach ($array_datos as $service_id => $service) {
            //si es el show que yo quiero mostrar
            if ($service_id_mostrar == $service_id) {

                foreach ($fechas_usadas as $fecha) {
                    $valor_elegido = 0;
                    if (isset($service[$fecha])) {
                        $dato = $service[$fecha];

                        if (isset($dato[$valor_mostrar])) {
                            $valor_elegido = $dato[$valor_mostrar];
                        } else {
                            //si es la ganancia
                            $valor_elegido = ($dato['costo_cliente'] - $dato['costo_empleados'] - $dato['costo_proveedor']);
                        }
                    }
                    $array_valores[] = $valor_elegido;
                }
                break;
            }
        }


        /* Create and populate the pData object */
        $MyData = new pData();
        $MyData->addPoints($array_valores, $services[$service_id]);
        $MyData->setSerieWeight($services[$service_id], 2);
        $MyData->setAxisName(0, $tipos_valores[$valor_mostrar]);
        $MyData->addPoints($fechas_usadas, "Meses");
        $MyData->setSerieDescription("Meses", "Meses");
        $MyData->setAbscissa("Meses");

        /* Create the pChart object */
        $myPicture = new pImage(700, 230, $MyData);
        /* Turn of Antialiasing */
        $myPicture->Antialias = FALSE;

        /* Draw the background */
        $Settings = array("R" => 255, "G" => 255, "B" => 255, "Dash" => 1, "DashR" => 255, "DashG" => 255, "DashB" => 255);
        $myPicture->drawFilledRectangle(0, 0, 700, 230, $Settings);

        /* Write the chart title */
        //$myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8, "R" => 255, "G" => 255, "B" => 255));
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 8, "R" => 0, "G" => 0, "B" => 0));
        $myPicture->drawText(10, 16, $services[$service_id], array("FontSize" => 11, "Align" => TEXT_ALIGN_BOTTOMLEFT));

        /* Set the default font */
        $myPicture->setFontProperties(array("FontName" => SITE_PATH . "/app/vendors/pchart/fonts/verdana.ttf", "FontSize" => 6, "R" => 0, "G" => 0, "B" => 0));

        /* Define the chart area */
        $myPicture->setGraphArea(60, 40, 650, 200);

        /* Draw the scale */
        $scaleSettings = array("XMargin" => 10, "YMargin" => 10, "Floating" => TRUE, "GridR" => 200, "GridG" => 200, "GridB" => 200, "DrawSubTicks" => TRUE, "CycleBackground" => TRUE);
        $myPicture->drawScale($scaleSettings);

        /* Turn on Antialiasing */
        $myPicture->Antialias = TRUE;

        /* Enable shadow computing */
        $myPicture->setShadow(TRUE, array("X" => 1, "Y" => 1, "R" => 0, "G" => 0, "B" => 0, "Alpha" => 10));

        /* Draw the line chart */
        $myPicture->drawLineChart();
        $myPicture->drawPlotChart(array("DisplayValues" => TRUE, "PlotBorder" => TRUE, "BorderSize" => 2, "Surrounding" => -60, "BorderAlpha" => 80));

        /* Write the chart legend */
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 255, "FontG" => 255, "FontB" => 255));
        //$myPicture->drawLegend(590, 9, array("Style" => LEGEND_NOBORDER, "Mode" => LEGEND_HORIZONTAL, "FontR" => 0, "FontG" => 0, "FontB" => 0));

        /* Render the picture (choose the best way) */
        $myPicture->autoOutput("pictures/example.drawLineChart.plots.png");
    }

    function imprimir_employee_stats() {
        
        Configure::write('debug', 0);
        $this->layout = 'ajax';

        $datos = $this->Session->read('data_stats_employee');

        $monto_total = 0;
        $total_pagado = 0;
        $total_a_pagar = 0;

        //busco los datos del empleado
        $employee = $this->Employee->find('first', array(
            'recursive' => -1,
            'conditions' => array(
                'Employee.id' => $datos['filtros']['employee_id']
            )
                ));

        //require(SITE_PATH . "/app/vendors/fpdf/fpdf.php");
        App::import('Vendor', 'WellNamed', array('file' => 'fpdf' . DS . 'fpdf.php'));

        $pdf = new FPDF();
        $pdf->AliasNbPages();
        $pdf->AddPage();

        $pdf->SetFont('Arial', '', 7);

        $margen_arriba = 12;
        $margen_izquierdo = 12;
        $margen_derecho = 12;
        $pdf->SetTopMargin($margen_arriba);
        $pdf->SetLeftMargin($margen_izquierdo);
        $pdf->SetRightMargin($margen_derecho);

        //defino el salto de linea
        $salto_linea = 0;
        $salto_trabajo = 1;

        //defino el alto de cada linea
        $alto_linea = 3;

        /*         * ************************************************** */
        /*         * *  empieza el encabezado  ** */
        /*         * ************************************************** */

        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(50, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Resumen de pago'));
        $pdf->Ln($alto_linea);
        $pdf->Ln($alto_linea);
        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(50, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Nombre: ' . $employee['Employee']['first_name'] . ' ' . $employee['Employee']['last_name']));
        $pdf->Ln($alto_linea);
        $pdf->Ln($alto_linea);
        $aux_fecha_inicio = $datos['filtros']['date_from'];
        $aux_fecha_fin = $datos['filtros']['date_to'];
        $pdf->Cell(50, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Rango de fechas: desde el ' . $aux_fecha_inicio . ' hasta el ' . $aux_fecha_fin));
        $pdf->Ln($alto_linea);

        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        /*         * ************************************************** */
        /*         * *  empieza cumpleaños  ** */
        /*         * ************************************************** */

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Cumpleaños'));
        $pdf->SetFont('Arial', 'B', 7);
        $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Fecha'));
        $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Dirección'));
        $pdf->Cell(60, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Tareas'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Monto'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Estado'));
        $pdf->SetFont('Arial', '', 7);
        if (empty($datos['birthdays'])) {
            $pdf->Ln($alto_linea);
            $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'No se registra ningún Cumpleaños'));
        }

        $i = 0;
        $total_birthday = 0;
        $aux_no_pagado = 0;
        $cant_birthday_paid = 0;
        foreach ($datos['birthdays'] as $stat_birthday):
            $class = null;
            if ($i++ % 2 == 0) {
                $class = ' class="altrow"';
            }
            $pdf->Ln($alto_linea);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_birthday['date']));
            if (strlen($stat_birthday['address']) > 30) {
                //si es mas largo que 40, lo corto
                $stat_birthday['address'] = substr($stat_birthday['address'], 0, 30) . '...';
            }
            $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_birthday['address']));
            $pdf->Cell(60, $alto_linea, iconv('utf-8', 'ISO-8859-1', implode(', ', $stat_birthday['details'])));
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_birthday['cost'] != NULL ? '$' . $stat_birthday['cost'] : '$0'));
            $monto_total += $stat_birthday['cost'] != NULL ? $stat_birthday['cost'] : 0;
            if ($stat_birthday['paid'] == 0) {
                $estado = 'No Pagado';
                $class = 'no_paid';
                $total_a_pagar += $stat_birthday['cost'] != NULL ? $stat_birthday['cost'] : 0;
                $aux_no_pagado = 1;
                $algo_sin_pagar = 1;
            } else {
                $estado = 'Pagado';
                $class = 'paid';
                $cant_birthday_paid++;
                $total_pagado += $stat_birthday['cost'] != NULL ? $stat_birthday['cost'] : 0;
            }
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $estado));

            $total_birthday += $stat_birthday['cost'] != NULL ? $stat_birthday['cost'] : 0;
        endforeach;

        if (!empty($datos['birthdays'])) {
            $pdf->Ln($alto_linea);
            $pdf->Ln($alto_linea);
            $pdf->SetFont('Arial', 'B', 7);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Total'));
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', '$' . $total_birthday));
            $pdf->Ln($alto_linea);
        }

        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        /*         * ************************************************** */
        /*         * *  empieza eventos  ** */
        /*         * ************************************************** */

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Eventos'));
        $pdf->SetFont('Arial', 'B', 7);
        $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Fecha'));
        $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Dirección'));
        $pdf->Cell(60, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Tareas'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Monto'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Estado'));
        $pdf->SetFont('Arial', '', 7);
        if (empty($datos['events'])) {
            $pdf->Ln($alto_linea);
            $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'No se registra ningún Evento'));
        }

        $i = 0;
        $total_event = 0;
        $aux_no_pagado = 0;
        $cant_event_paid = 0;
        foreach ($datos['events'] as $stat_event):
            $class = null;
            if ($i++ % 2 == 0) {
                $class = ' class="altrow"';
            }
            $pdf->Ln($alto_linea);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_event['date']));
            if (strlen($stat_event['address']) > 30) {
                //si es mas largo que 30, lo corto
                $stat_event['address'] = substr($stat_event['address'], 0, 30) . '...';
            }
            $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_event['address']));
            $pdf->Cell(60, $alto_linea, iconv('utf-8', 'ISO-8859-1', implode(', ', $stat_event['details'])));
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_event['cost'] != NULL ? '$' . $stat_event['cost'] : '$0'));
            $monto_total += $stat_birthday['cost'] != NULL ? $stat_birthday['cost'] : 0;
            if ($stat_event['paid'] == 0) {
                $estado = 'No Pagado';
                $class = 'no_paid';
                $total_a_pagar += $stat_event['cost'] != NULL ? $stat_event['cost'] : 0;
                $aux_no_pagado = 1;
                $algo_sin_pagar = 1;
            } else {
                $estado = 'Pagado';
                $class = 'paid';
                $cant_event_paid++;
                $total_pagado += $stat_event['cost'] != NULL ? $stat_event['cost'] : 0;
            }
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $estado));
?>
            <?php

            $total_event += $stat_event['cost'] != NULL ? $stat_event['cost'] : 0;
        endforeach;

        if (!empty($datos['events'])) {
            $pdf->Ln($alto_linea);
            $pdf->Ln($alto_linea);
            $pdf->SetFont('Arial', 'B', 7);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Total'));
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', '$' . $total_event));
            $pdf->Ln($alto_linea);
        }


        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        /*         * ************************************************** */
        /*         * *  empieza obras de teatro  ** */
        /*         * ************************************************** */

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Obras de Teatro'));
        $pdf->SetFont('Arial', 'B', 7);
        $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Fecha'));
        $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Dirección'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Monto'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Estado'));
        $pdf->SetFont('Arial', '', 7);
        if (empty($datos['theaters'])) {
            $pdf->Ln($alto_linea);
            $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'No se registra ninguna Obra de Teatro'));
        }

        $i = 0;
        $total_theater = 0;
        $aux_no_pagado = 0;
        $cant_theater_paid = 0;
        foreach ($datos['theaters'] as $stat_theater):
            $class = null;
            if ($i++ % 2 == 0) {
                $class = ' class="altrow"';
            }
            $pdf->Ln($alto_linea);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_theater['date']));
            if (strlen($stat_theater['address']) > 30) {
                //si es mas largo que 30, lo corto
                $stat_theater['address'] = substr($stat_theater['address'], 0, 30) . '...';
            }
            $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_theater['address']));
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_theater['cost'] != NULL ? '$' . $stat_theater['cost'] : '$0'));
            $monto_total += $stat_theater['cost'] != NULL ? $stat_theater['cost'] : 0;
            if ($stat_theater['paid'] == 0) {
                $estado = 'No Pagado';
                $class = 'no_paid';
                $total_a_pagar += $stat_theater['cost'] != NULL ? $stat_theater['cost'] : 0;
                $aux_no_pagado = 1;
                $algo_sin_pagar = 1;
            } else {
                $estado = 'Pagado';
                $class = 'paid';
                $cant_theater_paid++;
                $total_pagado += $stat_theater['cost'] != NULL ? $stat_theater['cost'] : 0;
            }
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $estado));
            ?>
            <?php

            $total_theater += $stat_theater['cost'] != NULL ? $stat_theater['cost'] : 0;
        endforeach;

        if (!empty($datos['theaters'])) {
            $pdf->Ln($alto_linea);
            $pdf->Ln($alto_linea);
            $pdf->SetFont('Arial', 'B', 7);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Total'));
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', '$' . $total_theater));
            $pdf->Ln($alto_linea);
        }

        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        /*         * ************************************************** */
        /*         * *  empieza gastos extra  ** */
        /*         * ************************************************** */

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Gastos Extra'));
        $pdf->SetFont('Arial', 'B', 7);
        $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Fecha'));
        $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Descripción'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Monto'));
        $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Estado'));
        $pdf->SetFont('Arial', '', 7);
        if (empty($datos['expenses'])) {
            $pdf->Ln($alto_linea);
            $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'No se registra ningún Gasto Extra'));
        }

        $i = 0;
        $total_expense = 0;
        $aux_no_pagado = 0;
        $cant_expense_paid = 0;
        foreach ($datos['expenses'] as $stat_expense):
            $class = null;
            if ($i++ % 2 == 0) {
                $class = ' class="altrow"';
            }
            $pdf->Ln($alto_linea);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_expense['date']));
            if (strlen($stat_expense['description']) > 30) {
                //si es mas largo que 30, lo corto
                $stat_expense['description'] = substr($stat_expense['description'], 0, 30) . '...';
            }
            $pdf->Cell(40, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_expense['description']));
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $stat_expense['value'] != NULL ? '$' . $stat_expense['value'] : '$0'));
            $monto_total += $stat_expense['value'] != NULL ? $stat_expense['value'] : 0;
            if ($stat_expense['paid'] == 0) {
                $estado = 'No Pagado';
                $class = 'no_paid';
                $total_a_pagar += $stat_expense['value'] != NULL ? $stat_expense['value'] : 0;
                $aux_no_pagado = 1;
                $algo_sin_pagar = 1;
            } else {
                $estado = 'Pagado';
                $class = 'paid';
                $cant_expense_paid++;
                $total_pagado += $stat_expense['value'] != NULL ? $stat_expense['value'] : 0;
            }
            $pdf->Cell(15, $alto_linea, iconv('utf-8', 'ISO-8859-1', $estado));
            ?>
            <?php

            $total_expense += $stat_expense['value'] != NULL ? $stat_expense['value'] : 0;
        endforeach;

        if (!empty($datos['expenses'])) {
            $pdf->Ln($alto_linea);
            $pdf->Ln($alto_linea);
            $pdf->SetFont('Arial', 'B', 7);
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Total'));
            $pdf->Cell(25, $alto_linea, iconv('utf-8', 'ISO-8859-1', '$' . $total_expense));
        }


        //obtengo el valor de ubicacion vertical
        $pdf->Ln($alto_linea);
        $y = $pdf->GetY();
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);
        $pdf->Line($margen_izquierdo, $y, 190, $y);
        $pdf->Ln($alto_linea);

        $pdf->SetFont('Arial', 'B', 8);
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Monto Total =   $' . $monto_total));
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Total Pagado = $' . $total_pagado));
        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Total a Pagar = $' . $total_a_pagar));
        $pdf->Ln($alto_linea);
        $pdf->Ln($alto_linea);
        $texto = '';
        if ($cant_birthday_paid > 0) {
            if ($cant_birthday_paid > 1) {
                $texto .= $cant_birthday_paid . ' cumpleaños, ';
            } else {
                $texto .= $cant_birthday_paid . ' cumpleaño, ';
            }
        }
        if ($cant_event_paid > 0) {
            if ($cant_event_paid > 1) {
                $texto .= $cant_event_paid . ' eventos, ';
            } else {
                $texto .= $cant_event_paid . ' evento, ';
            }
        }
        if ($cant_theater_paid > 0) {
            if ($cant_theater_paid > 1) {
                $texto .= $cant_theater_paid . ' obras de teatro, ';
            } else {
                $texto .= $cant_theater_paid . ' obra de teatro, ';
            }
        }
        if ($cant_expense_paid > 0) {
            if ($cant_expense_paid > 1) {
                $texto .= $cant_expense_paid . ' gastos extra, ';
            } else {
                $texto .= $cant_expense_paid . ' gasto extra, ';
            }
        }
        //le saco la ultima coma
        if (substr($texto, -2, 2) == ', ') {
            $texto = substr($texto, 0, -2);
        }

        $pdf->MultiCell(0, $alto_linea + 3, iconv('utf-8', 'ISO-8859-1', 'Recibí de Lucila Peroche, la suma de $' . $total_a_pagar . ' en concepto de pago por ' . $texto . ' realizados desde el ' . date('d/m/Y', strtotime($datos['filtros']['date_from'])) . ' hasta el ' . date('d/m/Y', strtotime($datos['filtros']['date_to']))));
        $pdf->Ln($alto_linea);
        $pdf->Ln($alto_linea);
        $pdf->Cell(50, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Firma:'));
        $pdf->Cell(50, $alto_linea, iconv('utf-8', 'ISO-8859-1', 'Aclaración'));
        $pdf->Output();
        die;
    }

}
